<?

//Data loading
$title='REKAP UMUR PIUTANG';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. AMPEL JAYA';

$page   = 'PAGE';

$DEFINE_FONT=0;
$pdf=new PDFReport('L',"mm","Letter");
$pdf->AddPage();
$Result = GridList ();
if ($Result) $pdf->GridTable($Result);

$pdf->Output();

function GetFromDate () {
//  list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
    return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
    list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
    return array($LMonth,$LYear,$LNMonth);
}

function FlushCustomer ($TableName, $tmp) {
    global $DBConnection,$DBName,$DBGeneral;

    if ($tmp) $TEMPORARY = "TEMPORARY";

    //$SQL = "DROP TABLE IF EXISTS `$TableName`;";
    //$DBConnection->dbc->query($SQL);

    $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
              `cucode` varchar(20) NOT NULL default '',
              `cunama` varchar(50) default NULL,
              `top` int(4) default NULL

              )";
    $DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);

    return true;
}

function GridList () {
    global $DBConnection;
    $TEMP = "TEMPORARY";

    if (!FlushCustomer ("ais_customer", 1)) return false;
    //$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

    list($day,$month,$year) = explode("/",GetParam("agingDate",""));
    $current_date = "$year-$month-$day";

    $SQL = "DELETE FROM `ais_piutang_open`";
    $DBConnection->dbc->query($SQL);

    $SQL = "INSERT INTO ais_piutang_open
        SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, tbldebtur.cucode, tbldebtur.cuname, URAIAN, CURR, JUMLAH, IFNULL(top,0) AS TOP,
        ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ) as top_curr,
        ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ) as top_30,
        ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ) as top_60,
        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))
        FROM ais_piutang
        LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
        WHERE srt='1' AND ais_piutang.tglbkt<='$current_date' ";

    if (GetParam("fromSkada",""))
        $SQL .= "AND ais_piutang.skada >= '".GetParam("fromSkada","")."' ";

    if (GetParam("curr",""))
        $SQL .= "AND ais_piutang.curr = '".GetParam("curr","")."' ";

    if (GetParam("toSkada",""))
        $SQL .= "AND ais_piutang.skada <= '".GetParam("toSkada","")."' ";

        //print $SQL;die();
    if (GetParam("office",""))  $SQL .= "AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
    $SQL .= "order by nobkt ";
//die($SQL);
    $DBConnection->dbc->query($SQL);

    $SQL = "DELETE FROM `ais_piutang_close`";
    $DBConnection->dbc->query($SQL);

    $SQL = "INSERT INTO ais_piutang_close
        SELECT NOBKT, NOBKTA, SUM(JUMLAH),debtur,debnam
        FROM ais_piutang
        LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
        WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";

    //print $SQL;die();
    if (GetParam("office",""))
        $SQL .= "AND ais_piutang.ktrasl = '".GetParam("office","")."' ";

    if (GetParam("curr",""))
        $SQL .= "AND ais_piutang.curr = '".GetParam("curr","")."' ";

        $SQL .= "group by nobkta order by nobkta";
                        //print $SQL;die();
    $DBConnection->dbc->query($SQL);
            //$DBConnection->dbc->debug();

//Auto Update Using Cache Begin Here
    $SQL = "DELETE FROM `ais_piutang_hasil`";
    $DBConnection->dbc->query($SQL);

    $SQL = "
        INSERT INTO ais_piutang_hasil
        SELECT
            ais_piutang_open.ktrasl,
            ais_piutang_open.tglbkt,
            '$current_date',
            ais_piutang_open.nobkt,
            ais_piutang_open.debtur,
            ais_piutang_open.debnam,
            ais_piutang_open.uraian,
            ifnull(ais_piutang_open.top, 0) AS top,
            ifnull(ais_piutang_open.jumlah,0)-ifnull(ais_piutang_close.bayar,0),
            IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_open.top DAY ),'0',IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_open.top DAY ),'30',IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_open.top DAY ),'60','60g'))),
            '".GetParam("fromSkada","")."',
            '".GetParam("toSkada","")."',
            '".GetParam("curr","")."'

        FROM
            ais_piutang_open
            LEFT JOIN ais_piutang_close ON (ais_piutang_open.nobkt = ais_piutang_close.nobkta)
            WHERE (ifnull(ais_piutang_close.nobkt, '') = '') OR ifnull(ais_piutang_open.jumlah-ais_piutang_close.bayar,0) <> 0
            ORDER BY debtur,nobkt ";
    $DBConnection->dbc->query($SQL);


    $TableName = "ais_piutang_hasil ";

    $SQL = "
        SELECT
            ktrasl,
            nobkt,
            debtur,
            debnam,
            uraian,
            cr_term as top,
            SUM(if($TableName.aging='0',jumlah,0)) AS jumlahcurr,
            SUM(if($TableName.aging='30',jumlah,0)) AS jumlah30,
            SUM(if($TableName.aging='60',jumlah,0)) AS jumlah60,
            SUM(if($TableName.aging='60g',jumlah,0)) AS jumlah60g
        FROM $TableName
        WHERE aging_date='$current_date' AND fromskada='".GetParam("fromSkada","")."' AND
            toskada='".GetParam("toSkada","")."' AND curr='".GetParam("curr","")."'
        GROUP BY debtur ORDER BY debnam ";

//die($SQL);
    return $DBConnection->dbc->get_results($SQL, ARRAY_A);
}

function ReadCOA($no) {
            $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
            $SQL = "SELECT namacc FROM tblcoa WHERE skada='".$no."'";
            $MyResult = $DBConnection->dbc->get_var($SQL);
            return $MyResult;
}

class PDFReport extends PDF {

    function Header() {
        global $title,$perush,$page,$DEFINE_FONT;
        if ($DEFINE_FONT==0) {
            $this->AddFont('Arial','','arialn.php');
            $DEFINE_FONT=1;
        }

        list($Day,$Month,$Year,$NMonth) = GetFromDate();
        list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

        $this->SetFont('Arial','',10);
            $this->Cell(260,5,$perush,0,1,'C',0);
            $this->Cell(260,5,$title,0,1,'C',0);
            $this->Cell(260,5,"AS OF: ".GetParam("agingDate",""),0,1,'C',0);
        $this->Cell(260,5,"SKADA: ".GetParam("fromSkada","")." - ".ReadCOA(GetParam("fromSkada","")),0,1,'L',0);
        $this->Cell(130,5,"CURRENCY: ".GetParam("curr",""),0,0,'L',0);
        $this->Cell(130,5,$page." ".$this->PageNo(),0,1,'R',0);
        //      $this->Line(10,40,270,40);
        $this->Ln();
        //      $this->Ln();
        //Header

        //Column widths
        $w=array(20,80,10,30,30,30,30,30);
        $this->SetFont('Arial','',8);
        $this->Cell($w[0],5,"KTRASL",1,0,'C',0);
        $this->Cell($w[1],5,"DEBTUR",1,0,'C',0);
        $this->Cell($w[2],5,"CR",1,0,'C',0);
        $this->Cell($w[3],5,"CURRENT",1,0,'C',0);
        $this->Cell($w[4],5,"1-30",1,0,'C',0);
        $this->Cell($w[5],5,"31-60",1,0,'C',0);
        $this->Cell($w[6],5,">60",1,0,'C',0);
        $this->Cell($w[7],5,"TOTAL",1,0,'C',0);
        $this->Ln();

    }

    function GridTable($MyResult) {
        //Column widths
        $w=array(20,80,10,30,30,30,30,30);

        //Data
        foreach($MyResult as $row)
        {
        $row_total  = $row["jumlahcurr"]+$row["jumlah30"]+$row["jumlah60"]+$row["jumlah60g"];
        $this->Cell($w[0],5,GetParam("office",""),'LR');
            $this->Cell($w[1],5,$row["debtur"].' - '.$row["debnam"],'LR');
            $this->Cell($w[2],5,$row["top"],'LR',0,'R');
        $this->Cell($w[3],5,number_format($row["jumlahcurr"],2),'LR',0,'R');
            $this->Cell($w[4],5,number_format($row["jumlah30"],2),'LR',0,'R');
        $this->Cell($w[5],5,number_format($row["jumlah60"],2),'LR',0,'R');
        $this->Cell($w[6],5,number_format($row["jumlah60g"],2),'LR',0,'R');
        $this->Cell($w[7],5,number_format($row_total,2),'LR',0,'R');
            $this->Ln();
        $total_cur  = $total_cur + $row["jumlahcurr"];
        $total_30   = $total_30 + $row["jumlah30"];
        $total_60   = $total_60 + $row["jumlah60"];
        $total_60g  = $total_60g + $row["jumlah60g"];
        }
            $total_row  = $total_cur + $total_30 + $total_60 + $total_60g;
            $this->Cell($w[0]+$w[1]+$w[2],5,"TOTAL:",1,0,'R',0);
            $this->Cell($w[3],5,number_format($total_cur,2),1,0,'R',0);
            $this->Cell($w[4],5,number_format($total_30,2),1,0,'R',0);
            $this->Cell($w[5],5,number_format($total_60,2),1,0,'R',0);
        $this->Cell($w[6],5,number_format($total_60g,2),1,0,'R',0);
        $this->Cell($w[7],5,number_format($total_row,2),1,0,'R',0);
        $this->Ln();
        $this->Cell($w[0]+$w[1]+$w[2],5,"PERCENTAGE:",1,0,'R',0);
        $this->Cell($w[3],5,number_format($total_cur/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[4],5,number_format($total_30/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[5],5,number_format($total_60/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[6],5,number_format($total_60g/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[7],5,number_format($total_row/$total_row*100,2)."%",1,0,'R',0);
        $this->Ln();
        $this->LatestPage=true;

        //Closure line
    }

    function Footer() {
        global $DBConnection;
        $w=array(100,60,100);
        //Closure line
        $this->Cell(260,7,'','T');
        if ($this->LatestPage) {
            $this->Ln();
                $this->Cell($w[0],5,"MENYETUJUI",0,0,'C',0);
                $this->Cell($w[1],5,"",0);
                $this->Cell($w[2],5,"MEMBUAT",0,0,'C',0);
                $this->Ln();$this->Ln();
                $this->Ln();$this->Ln();
            $SQL = "SELECT * FROM $DBName.tblprofile WHERE ktrasl='".GetOfficeID()."'";
            $Result = $DBConnection->dbc->get_row($SQL);

                $this->Cell($w[0],5,"( ".$Result->ttdagp1." )",0,0,'C',0);
                $this->Cell($w[1],5,"",0);
                $this->Cell($w[2],5,"( ".$Result->ttdagp2." )",0,0,'C',0);

        }
    }

}


?>
